--    Program: 	  CAPCMIMP_CE_STATEMENT_LINES.sql
--    Description:  Import CM CE STATEMENT LINES information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Andy Mak 
--    Date:         8-May-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg			varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_header_id			number;
   l_line_id			number;
                    		                 		
   cursor cur_1 is
      select * from CRST_CE_STATEMENT_LINES;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_1 loop   	
     l_err_msg_tmp := '';
     
     l_header_id := null; 	
     l_line_id   := null;

     begin
       SELECT statement_header_id_new
       INTO   l_header_id
       FROM   CRST_CE_STATEMENT_HEADERS_ALL
       WHERE  statement_header_id = cur.statement_header_id;
     exception
       when others then
         l_err_msg_tmp := 'New Statement Header ID '||l_header_id||' not found';
     end;

     if l_header_id is not null then
	  select CE_STATEMENT_LINES_S.NEXTVAL 
     		into l_line_id
     			FROM DUAL;
     end if;
     
     update CRST_CE_STATEMENT_LINES     
	set    STATEMENT_HEADER_ID_NEW = l_header_id,
	       STATEMENT_LINE_ID_NEW = l_line_id
     where  statement_header_id = cur.statement_header_ID
      and statement_line_id = cur.statement_line_id;
     

     begin
       -- import data
       insert into CE_STATEMENT_LINES
       (
  		STATEMENT_LINE_ID            ,
  		STATEMENT_HEADER_ID          ,
  		LINE_NUMBER                  ,
  		TRX_DATE                     ,
  		TRX_TYPE                     ,
  		AMOUNT                       ,
  		STATUS                       ,
  		CREATED_BY                   ,
  		CREATION_DATE                ,
  		LAST_UPDATED_BY              ,
  		LAST_UPDATE_DATE             ,
  		TRX_CODE_ID                  ,
  		EFFECTIVE_DATE               ,
  		BANK_TRX_NUMBER              ,
  		TRX_TEXT                     ,
  		CUSTOMER_TEXT                ,
  		INVOICE_TEXT                 ,
  		BANK_ACCOUNT_TEXT            ,
  		CURRENCY_CODE                ,
  		EXCHANGE_RATE_TYPE           ,
  		EXCHANGE_RATE                ,
  		EXCHANGE_RATE_DATE           ,
  		ORIGINAL_AMOUNT              ,
  		CHARGES_AMOUNT               ,
  		LAST_UPDATE_LOGIN            ,
  		RECONCILE_TO_STATEMENT_FLAG  ,
  		ATTRIBUTE_CATEGORY           ,
  		ATTRIBUTE1                   ,
  		ATTRIBUTE2                   ,
  		ATTRIBUTE3                   ,
  		ATTRIBUTE4                   ,
  		ATTRIBUTE5                   ,
  		ATTRIBUTE6                   ,
  		ATTRIBUTE7                   ,
  		ATTRIBUTE8                   ,
  		ATTRIBUTE9                   ,
  		ATTRIBUTE10                  ,
  		ATTRIBUTE11                  ,
  		ATTRIBUTE12                  ,
  		ATTRIBUTE13                  ,
  		ATTRIBUTE14                  ,
  		ATTRIBUTE15                  ,
  		CE_STATEMENT_LINES            
       )
       values
       (   
  		l_line_id	          ,
  		l_header_id	     	     ,
  	        cur.LINE_NUMBER                  ,
  		cur.TRX_DATE                     ,
  		cur.TRX_TYPE                     ,
  		cur.AMOUNT                       ,
  		cur.STATUS                       ,
  		CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
  		cur.CREATION_DATE                ,
  		CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
  		cur.LAST_UPDATE_DATE             ,
  		cur.TRX_CODE_ID                  ,
  		cur.EFFECTIVE_DATE               ,
  		cur.BANK_TRX_NUMBER              ,
  		cur.TRX_TEXT                     ,
  		cur.CUSTOMER_TEXT                ,
  		cur.INVOICE_TEXT                 ,
  		cur.BANK_ACCOUNT_TEXT            ,
  		cur.CURRENCY_CODE                ,
  		cur.EXCHANGE_RATE_TYPE           ,
  		cur.EXCHANGE_RATE                ,
  	        cur.EXCHANGE_RATE_DATE           ,
  		cur.ORIGINAL_AMOUNT              ,
  		cur.CHARGES_AMOUNT               ,
  		cur.LAST_UPDATE_LOGIN            ,
  		cur.RECONCILE_TO_STATEMENT_FLAG  ,
  		cur.ATTRIBUTE_CATEGORY           ,
  		cur.ATTRIBUTE1                   ,
  		cur.ATTRIBUTE2                   ,
  		cur.ATTRIBUTE3                   ,
  		cur.ATTRIBUTE4                   ,
  		cur.ATTRIBUTE5                   ,
  		cur.ATTRIBUTE6                   ,
  		cur.ATTRIBUTE7                   ,
  		cur.ATTRIBUTE8                   ,
  		cur.ATTRIBUTE9                   ,
  		cur.ATTRIBUTE10                  ,
  		cur.ATTRIBUTE11                  ,
  		cur.ATTRIBUTE12                  ,
  		cur.ATTRIBUTE13                  ,
  		cur.ATTRIBUTE14                  ,
  		cur.ATTRIBUTE15                  ,
  		cur.CE_STATEMENT_LINES            
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if (l_err_msg_tmp = '' or l_err_msg_tmp is null) then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   if l_err_msg is null then
     l_err_msg := 'No Error';
   end if; 
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPCMIMP_CE_STATEMENT_LINES', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


exit
